| |
4608 Search Results Found |
| 1 |
Forum: Server Administration «»
Posted on: Thu, 13 April 2023 06:25 «»
By: Andrey_R
No SCN in controlfile after BACKUP TO TRACE
…DBAs,
I've lived my DBA life so far knowing that the Control file contains among the rest the SCN of the database,
So it can manage the Backup & Recovery mechanism as well as the recovery during any startup & Recovery operations.
…
|
| 2 |
Forum: Data Guard «»
Posted on: Sat, 11 February 2023 11:31 «»
By: Andrey_R
Re: RMAN-06820 on rman backup from standby with OS authentication
…you actually do the test, rather than merely speculating, it may become clear that the datafile backup is 100% useless without the redo required to make it consistent.
That's a fair point. So I did:
- Install Oracle 19c EE, no PDB, on Windows Server…
|
| 3 |
Forum: SQL & PL/SQL «»
Posted on: Wed, 05 April 2023 16:52 «»
By: Barbara Boehmer
Re: difficult task
…
It looks like the data to be put in the positions table must come from the deals table. So, you will need to loop through the rows of the deals table that match the date parameter and process them, inserting into or updating or deleting from the …
|
| 4 |
Forum: SQL & PL/SQL «»
Posted on: Tue, 28 February 2023 13:32 «»
By: Barbara Boehmer
Re: how to incrementally get all numbers incremented by n between 2 columns values
…is another possible version with tests of various number values for n.
SCOTT@orcl_12.1.0.2.0> -- test_data:
SCOTT@orcl_12.1.0.2.0> select * from test_data order by instance_number, snap_id
2 /
INSTANCE_NUMBER SNAP_ID…
|
| 5 |
Forum: SQL & PL/SQL «»
Posted on: Tue, 28 February 2023 13:30 «»
By: Barbara Boehmer
Re: how to incrementally get all numbers incremented by n between 2 columns values
…see some problems with that. Please see my tests below using various number values for n, including 1, which returns no rows.
SCOTT@orcl_12.1.0.2.0> -- test_data:
SCOTT@orcl_12.1.0.2.0> select * from test_data order by instance_number, …
|
| 6 |
Forum: SQL & PL/SQL «»
Posted on: Fri, 24 November 2023 15:57 «»
By: Barbara Boehmer
Re: Sequentially update a column with a decrementing value
…
SCOTT@orcl_12.1.0.2.0> -- optional SQL*Plus format commands
SCOTT@orcl_12.1.0.2.0> -- to shorten column widths for
SCOTT@orcl_12.1.0.2.0> -- easier readability on this forum:
SCOTT@orcl_12.1.0.2.0> COLUMN LOC &…
|
| 7 |
Forum: SQL & PL/SQL «»
Posted on: Thu, 05 October 2023 15:11 «»
By: Barbara Boehmer
Re: Need a pl/sql block to be entered in existing package
…note how the following uses code tags that maintain indentation and align columns, making it easier to read. Also, please read all of the comments prefaced by hyphens.
-- create table statement that you should have provided:
SCOTT@orcl_12.…
|
| 8 |
Forum: SQL & PL/SQL «»
Posted on: Wed, 05 April 2023 12:25 «»
By: Barbara Boehmer
Re: difficult task
…tables and such look good, although you may want to make changes later.
For your procedure, this is what you have so far.
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE PROCEDURE calculate_positions(
2 vInDateTime IN DATE;
3 )
4 AS
5 …
|
| 9 |
Forum: Oracle Fusion Apps & E-Business Suite «»
Posted on: Thu, 16 February 2023 00:33 «»
By: Barbara Boehmer
Re: function call with optional parameters
…get rid of the P default, as shown below. If there are any continuing problems, it is more likely due to how you are calling the function or other things outside the function.
SCOTT@orcl_12.1.0.2.0> -- table and sample data for testing
SCOTT@…
|
| 10 |
Forum: SQL & PL/SQL «»
Posted on: Tue, 21 March 2023 03:38 «»
By: Barbara Boehmer
Re: convert xml column value into columns
…are getting everything all in one column because you are concatenating line feeds. If you want separate columns, then you need to select them as columns followed by any aliases and separated by commas. You also need to use some method such as column …
|
| 11 |
Forum: SQL & PL/SQL «»
Posted on: Fri, 17 February 2023 17:42 «»
By: Barbara Boehmer
Re: Cast(Multiset())
…
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE type Template as object(template_text varchar2(100),
2 template_value varchar2(100));
3 /
Type created.
SCOTT@orcl_12.1.0.2.0>
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE type …
|
| 12 |
Forum: SQL & PL/SQL «»
Posted on: Thu, 16 February 2023 15:40 «»
By: Barbara Boehmer
Re: Cast(Multiset())
…just had another thought. My initial response assumed that the template is always the fourth column. If it may be in any position, then you will need to loop through the columncount checking the column_name to get the position prior to using dbms_sql.…
|
| 13 |
Forum: SQL & PL/SQL «»
Posted on: Wed, 15 February 2023 23:08 «»
By: Barbara Boehmer
Re: Cast(Multiset())
…made a few changes to your parse procedure and marked them with comment lines along the left margin.
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE type Template as object(template_text varchar2(100),
2 template_value varchar2(100));
…
|
| 14 |
Forum: Text & interMedia «»
Posted on: Mon, 16 January 2023 13:28 «»
By: Barbara Boehmer
Re: Materialized View with Context Index
…can use refresh on commit in your create or alter of your materialized view and you can use sync(on commit) in your create or alter of your context index. You can optimize from a trigger if you use pragama_autonomous transaction. However, this may not …
|
| 15 |
Forum: SQL & PL/SQL «»
Posted on: Wed, 17 May 2023 15:34 «»
By: Barbara Boehmer
Re: check all the distinct value match
…am guessing that this is just a small sample of data and that speed of the query on a larger set of data might be important. In that case, I think the below query might be faster.
SELECT call_number
FROM call_details
WHERE …
|
| 16 |
Forum: SQL & PL/SQL «»
Posted on: Wed, 08 February 2023 20:27 «»
By: Barbara Boehmer
Re: Convert Date formats
…should be comparing dates as dates, not converting them to character strings and comparing those.
Assuming that you have the following table and data
SCOTT@orcl_12.1.0.2.0> create table mytable (date2 date)
2 /
Table created.
SCOTT@…
|
| 17 |
Forum: SQL & PL/SQL «»
Posted on: Wed, 08 February 2023 17:00 «»
By: Barbara Boehmer
Re: Convert Date formats
…is unclear what you are trying to do, whether you are trying to convert a date to a character string in a specific format, which is what to_char is for, or you are trying to convert a character string in a specific format to a date, which is what to_date …
|
| 18 |
Forum: SQL & PL/SQL «»
Posted on: Wed, 17 April 2024 16:46 «»
By: Barbara Boehmer
Re: Oracle ORA-00918: column ambiguously defined
…I am trying to understand what causes an error, I try to narrow it down to the simplest example that I can that reproduces the error and the simplest thing that resolves it. Please see the simplified demonstration below in which the problem is …
|
| 19 |
Forum: Server Utilities «»
Posted on: Tue, 16 April 2024 08:17 «»
By: Barbara Boehmer
Re: impd data only on the same database from one table to another table
…don't know if this is considered a loopback database link or not, but if you create a database link to the same database and use that as if it were a network link, then it works, as shown below.
SCOTT@orcl_12.1.0.2.0> CONN scott/tiger@orcl
…
|
| 20 |
Forum: SQL & PL/SQL «»
Posted on: Tue, 28 February 2023 10:35 «»
By: Barbara Boehmer
Re: how to incrementally get all numbers incremented by n between 2 columns values
…
SCOTT@orcl_12.1.0.2.0> -- test_data:
SCOTT@orcl_12.1.0.2.0> select * from test_data order by instance_number, snap_id
2 /
INSTANCE_NUMBER SNAP_ID
--------------- ----------
1 165949
1 165950…
|
| 21 |
Forum: SQL & PL/SQL «»
Posted on: Thu, 16 February 2023 09:49 «»
By: Barbara Boehmer
Re: Cast(Multiset())
…find the cursor expression easier to write and work with. The DBMS_SQL seems like kind of an old-fashioned, outdated way to do things. However, I have not done any tests to see which is more efficient. Perhaps some others will comment on that. In the …
|
| 22 |
Forum: Text & interMedia «»
Posted on: Mon, 16 January 2023 11:28 «»
By: Barbara Boehmer
Re: Oracle Text Search - Handling special characters and blank search term
…the translate function, it translates each occurrence of the character in the second parameter to the corresponding character in the third parameter, but just removes others. In the original example, there were only three special characters, so I used …
|
| 23 |
Forum: Text & interMedia «»
Posted on: Tue, 10 January 2023 12:15 «»
By: Barbara Boehmer
Re: Oracle Text Search - Handling special characters and blank search term
…are a number of issues here. One issue is that Oracle Text does not know what to do with the special characters. Another is that if the text query is not executed and there is no score then it does not know what to do when you have selected that …
|
| 24 |
Forum: SQL & PL/SQL «»
Posted on: Tue, 16 April 2024 10:52 «»
By: Barbara Boehmer
Re: Convert input from user into UPPERCASE
…is another method that works from SQL*Plus:
SCOTT@orcl_12.1.0.2.0> COLUMN muser NEW_VALUE v_user NOPRINT
SCOTT@orcl_12.1.0.2.0> ACCEPT muser CHAR PROMPT 'Give username :'
Give username :Test123
SCOTT@orcl_12.1.0.2.0> SELECT UPPER('&…
|
| 25 |
Forum: SQL & PL/SQL «»
Posted on: Fri, 09 February 2024 13:17 «»
By: Barbara Boehmer
Re: Xmlattribute not returning tag when the value is null
…am assuming that this is a simplification, and that your data comes from a table with multiple rows and that any value in any column in any row may be null. If this is the case, then after reviewing some of the links from the search link that Michel…
|
| 26 |
Forum: SQL & PL/SQL «»
Posted on: Sun, 17 December 2023 20:26 «»
By: Barbara Boehmer
Re: Problem in pivot query
…@OraFerro,
I strongly suspect that you have a minor typing error in your query that is not being posted here. You need to post a copy and paste of an actual complete run as I have done below, including the view creation, including line numbers, …
|
| 27 |
Forum: SQL & PL/SQL «»
Posted on: Mon, 30 October 2023 08:09 «»
By: Barbara Boehmer
Re: Create a oracle function that parse a string to date time when not null
…original query takes a string of characters representing a date in a specific format, converts it to a date, then back to a string in a different format. If you want a function that does the same thing, then your function should return VARCHAR2 data…
|
| 28 |
Forum: SQL & PL/SQL «»
Posted on: Mon, 18 September 2023 11:41 «»
By: Barbara Boehmer
Re: how to get employee in and out time for generated query based on first in and last out
…the following minimal example, dates with only one punch time will have the same date for in and out times. Also, whatever your first column is "M." (machine?) is ignored. It is ordered by the punch_date, which is really just a …
|
| 29 |
Forum: Text & interMedia «»
Posted on: Thu, 10 August 2023 19:05 «»
By: Barbara Boehmer
Re: How to determine last tiime Content was indexed
…see the following demonstration and answers below.
-- table, initial data, index, additional data for testing:
SCOTT@orcl_12.1.0.2.0> CREATE TABLE test_tab
2 (id NUMBER,
3 document VARCHAR2(…
|
| 30 |
Forum: SQL & PL/SQL «»
Posted on: Mon, 31 July 2023 22:46 «»
By: Barbara Boehmer
Re: How to Delimit the Large comma separated string - Oracle Procedure
…following is a copy of what I posted on the OTN forum in response to the same question.
https://forums.oracle.com/ords/apexds/post/how-to-delimit-the-large-comma-separated-string-oracle-proc-8084
It works for me, as demonstrated below. So, …
|
| 31 |
Forum: SQL & PL/SQL «»
Posted on: Wed, 28 June 2023 07:55 «»
By: Barbara Boehmer
Re: order by union query
…are multiple problems here.
lpad(adm_no) is not valid syntax, as shown below.
-- wrong:
SCOTT@orcl_12.1.0.2.0> select lpad(dname) from dept
2 /
select lpad(dname) from dept
*
ERROR at line …
|
| 32 |
Forum: Performance Tuning «»
Posted on: Sat, 03 June 2023 03:30 «»
By: Barbara Boehmer
Re: Bind by position
…something like the following:
bind by position:
SCOTT@orcl_12.1.0.2.0> var x1 refcursor
SCOTT@orcl_12.1.0.2.0> begin
2 open :x1 for
3 'select * from emp where deptno in (:x, :x, :x) order …
|
| 33 |
Forum: SQL & PL/SQL «»
Posted on: Fri, 27 January 2023 11:23 «»
By: Barbara Boehmer
Re: Two Column Join
…looks like your where clauss should produce the desired results, so you need to provide the things that have already been asked for and a better explanation of what it is that you want instead of what you are getting. Please see the following simulation …
|
| 34 |
Forum: Server Administration «»
Posted on: Tue, 24 May 2022 04:08 «»
By: shamsad.khan@gmail.com
Unable to connect to pluggable database - Oracle Datase 12.2.0.1.0
…
I have installed oracle database 12.2.0.1.0. as container database with orclpdb as my pluggabe database
Logged in sqlplus as connect sys as sysdba
ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON=FALSE SCOPE=BOTH;
create user c##test identified by …
|
| 35 |
Forum: SQL & PL/SQL «»
Posted on: Mon, 13 May 2024 20:38 «»
By: Barbara Boehmer
Re: previous month
…
SCOTT@orcl_12.1.0.2.0> -- optional SQL*Plus column format commands:
SCOTT@orcl_12.1.0.2.0> COLUMN "First Day of Previous Month" FORMAT A27
SCOTT@orcl_12.1.0.2.0> COLUMN "Last Day of Previous Month" FORMAT…
|
| 36 |
Forum: SQL & PL/SQL «»
Posted on: Sat, 16 December 2023 17:29 «»
By: Barbara Boehmer
Re: Problem in pivot query
…sum(wtd_year) to sum(wtd_amount)
SCOTT@orcl_12.1.0.2.0> select banner from v$version
2 /
BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12…
|
| 37 |
Forum: SQL & PL/SQL «»
Posted on: Fri, 25 August 2023 14:58 «»
By: Barbara Boehmer
Re: Getting Error - ORA-01858
…is already a date:
SCOTT@orcl_12.1.0.2.0> select CURRENT_DATE from dual
2 /
CURRENT_DATE
---------------
Fri 25-Aug-2023
If you try to use to_date on a date it is an unnecessary step that usually causes some error:
SCOTT…
|
| 38 |
Forum: Text & interMedia «»
Posted on: Thu, 10 August 2023 23:55 «»
By: Barbara Boehmer
Re: How to determine last tiime Content was indexed
…should be able to copy and paste the query below and run it.
SELECT TOKEN_TEXT
FROM DR$IMT_MSTR_INDX_URL1$I T
, MASTER_INDEX X
, DR$IMT_MSTR_INDX_URL1$K K
WHERE CONTAINS (X.…
|
| 39 |
Forum: SQL & PL/SQL «»
Posted on: Tue, 16 April 2024 10:35 «»
By: Barbara Boehmer
Re: Convert input from user into UPPERCASE
…to the OraFAQ forums. Here is a simplified demonstration of one method;
SCOTT@orcl_12.1.0.2.0> var muser VARCHAR2(10)
SCOTT@orcl_12.1.0.2.0> ACCEPT muser CHAR PROMPT 'Give username :'
Give username :Test123
SCOTT@orcl_12.1.0.2.0> …
|
| 40 |
Forum: SQL & PL/SQL «»
Posted on: Sat, 02 September 2023 13:07 «»
By: mathguy
Re: Jaro-Winkler similarity for 9i
…Barbara,
I took a look at your function from 2010, and unfortunately it has a bug. I found it by comparing my results to yours, seeing that they disagree in some cases, and then I read your code and found the problem.
Let me illustrate, and then I …
|
Blog
Search
Help
Members
Register
Login
Home